# -*- coding: UTF-8 -*-

import pandas as pd
from datetime import datetime

from pymongo.errors import DuplicateKeyError

from config import config
import numpy as np
import pymongo


def init_db():
    client = pymongo.MongoClient("mongodb://fyf:qwe@localhost:27017", connect=False)
    mongo_db = client["test"]
    # mongo_db.pick.create_index(
    #     [('tipper', pymongo.ASCENDING), ('match', pymongo.ASCENDING), ('pick', pymongo.ASCENDING),
    #      ('kickoff_time', pymongo.ASCENDING)],
    #     unique=True)
    # mongo_db.feed.create_index(
    #     [('tipper', pymongo.ASCENDING), ('match', pymongo.ASCENDING), ('pick', pymongo.ASCENDING),
    #      ('kickoff_time', pymongo.ASCENDING)],
    #     unique=True)
    # mongo_db.specialty.drop()
    # mongo_db.specialty.create_index(
    #     [('tipper', pymongo.ASCENDING),('level', pymongo.ASCENDING)],
    #     unique=True)

    # mongo_db.tipper.drop()
    # mongo_db.tipper.create_index(
    #     [('name', pymongo.ASCENDING)],
    #     unique=True)

    # mongo_db.email_tipper.drop()
    # mongo_db.email_tipper.create_index(
    #     [('name', pymongo.ASCENDING)],
    #     unique=True)

    mongo_db.rate.create_index(
        [('tipper', pymongo.ASCENDING), ('pick_type', pymongo.ASCENDING), ('sport', pymongo.ASCENDING),
         ('stake', pymongo.ASCENDING)],
        unique=True)


def connect():
    client = pymongo.MongoClient("mongodb://fyf:qwe@localhost:27017", connect=False)
    return client["test"]


def get_df_from_xsl(blogname):
    df = pd.read_excel(open(config.get_excel_name_s(blogname, 'a'), 'rb'), sheet_name='sheet1',
                       header=None,
                       names=['match', 'pick', 'odd', 'stake', 'pick_type', 'profit', 'result', 'sport', 'category',
                              'kickoff', 'kickoff_time'])
    pd.options.display.float_format = '{:,.1f}'.format
    pd.set_option('display.height', 1000)
    pd.set_option('display.max_rows', 500)
    pd.set_option('display.max_columns', 700)
    pd.set_option('display.width', 1000)
    return df


def bulk_insert(blog_name, data_list):
    # for row in data_list:
    real_insert_count = len(data_list)
    for index in range(len(data_list)):
        row = data_list[index]
        insert_item = {'tipper': blog_name,
                       'match': row[0],
                       'pick': row[1],
                       'odd': row[2],
                       'stake': row[3],
                       'pick_type': row[4],
                       'profit': row[5],
                       'result': row[6],
                       'sport': row[7],
                       'category': row[8],
                       'kickoff': row[9],
                       'kickoff_time': row[10],
                       'last_update': row[11],
                       'book_maker': row[12]}
        try:
            mongo_db.pick.insert(insert_item)
        except DuplicateKeyError:
            real_insert_count -= 1
            pass

    values = list(mongo_db.pick.find({'tipper': blog_name}))
    if real_insert_count:
        print(blog_name, 'total: ', len(values), ' , new real insert:  ', real_insert_count, '(', len(data_list), ')')
    else:
        print('no real insert count ')


def bulk_insert_feed(data_list):
    real_insert_count = 0
    for index in range(len(data_list)):
        row = data_list[index]
        insert_item = {'tipper': row[0],
                       'match': row[1],
                       'pick': row[2],
                       'odd': row[3],
                       'stake': row[4],
                       'pick_type': row[5],
                       'profit': row[6],
                       'result': row[7],
                       'sport': row[8],
                       'category': row[9],
                       'kickoff': row[10],
                       'kickoff_time': row[11],
                       'last_update': row[12],
                       'book_maker': row[13]}

        condition = {'tipper': insert_item['tipper'], 'match': insert_item['match'], 'pick': insert_item['pick'],
                     'book_maker': insert_item['book_maker'],
                     'kickoff_time': insert_item['kickoff_time']}
        feed_find = mongo_db.feed.find_one(condition)

        if feed_find is not None and 'is_read' in feed_find:
            insert_item['is_read'] = feed_find['is_read']
        else:
            insert_item['is_read'] = False
            real_insert_count = real_insert_count + 1
            print('new feed ! ', insert_item)

        replace_before = mongo_db.feed.find_one_and_replace(
            condition, insert_item, upsert=True)

        if replace_before:
            del replace_before['_id']

    if real_insert_count:
        print('total new feed: ', real_insert_count)
    else:
        print('no real feed inserted ')


def bulk_insert_tipper(data_list, is_renew):
    if is_renew:
        mongo_db.tipper.drop()
        mongo_db.tipper.create_index(
            [('name', pymongo.ASCENDING)],
            unique=True)

    for index in range(len(data_list)):
        try:
            row = data_list[index]
            insert_item = row
            mongo_db.tipper.insert(insert_item)
        except DuplicateKeyError:
            pass


def bulk_insert_email_tipper(data_list):
    mongo_db.email_tipper.drop()
    mongo_db.email_tipper.create_index(
        [('name', pymongo.ASCENDING)],
        unique=True)
    for index in range(len(data_list)):
        try:
            row = data_list[index]
            insert_item = row
            mongo_db.email_tipper.insert(insert_item)
        except DuplicateKeyError:
            pass


def single_insert_rate(rate_tipper_value, rate_sport_cate, rate_pick_type, rate_stake_slider, rate_score):
    print('single_insert_rate')

    if not rate_tipper_value or not rate_sport_cate or not rate_pick_type or not rate_stake_slider or not rate_score:
        print('single_insert_rate fail because empty  value')
        return
    try:
        mongo_db.rate.update({'tipper': rate_tipper_value, 'pick_type': rate_pick_type,
                              'sport': rate_sport_cate, 'stake': rate_stake_slider}, {
                                 'tipper': rate_tipper_value,
                                 'sport': rate_sport_cate,
                                 'pick_type': rate_pick_type,
                                 'stake': rate_stake_slider,
                                 'score': rate_score,
                             }, True)
    except DuplicateKeyError:
        pass


def remove_rate(rate_tipper_value):
    print('remove_last_rate')
    mongo_db.rate.remove({'tipper': rate_tipper_value})


def get_rate_list(rate_tipper_value):
    rate_list = list(mongo_db.rate.find({'tipper': rate_tipper_value}))
    rate_list = list(map(lambda x: x, rate_list))
    return rate_list


def get_tipper_list_from_online(min_pick_limit=10, max_pick_limit=300):
    tipper_list = list(
        mongo_db.tipper.find({'$and': [{'picks': {'$lt': max_pick_limit}}, {'picks': {'$gt': min_pick_limit}}]}))
    tipper_list = list(map(lambda x: x['name'], tipper_list))
    return tipper_list


def get_email_tipper_list_from_online():
    tipper_list = list(mongo_db.email_tipper.find())
    tipper_list = list(map(lambda x: x['name'], tipper_list))
    return tipper_list


def update_feed(feed_list):
    for feed in feed_list:
        update_result = mongo_db.feed.update_one(
            {'tipper': feed['tipper'], 'pick_type': feed['pick_type'], 'sport': feed['sport'], 'stake': feed['stake'],
             'book_maker': feed['book_maker'],
             'kickoff_time': feed['kickoff_time']},
            {'$set': {
                'is_read': feed['is_read']
            }}, upsert=False)
        print('update_result', update_result.raw_result)


mongo_db = connect()
# init_db()
# write_xsl_to_db('over7')
